Fritz: All Fritz/All Fritz.zip/All Fritz/FILES/SPREOTUS/123TECH.LZH/DATAFILL.TXT

Text File | 1984-11-24 | 8KB | 196 lines

           TIPS ON USING 1-2-3

           This time, our "Tips" column features the versatile /Data Fill
















           command and other helpful 1-2-3 pointers.  The /Data Fill command
           lives a double life.  As a regular 1-2-3 command, it comes in
           handy for putting sequences of numbers in ranges.  In macros, it
           becomes a powerful tool for putting numbers in cells anywhere in
           the worksheet, for performing calculations, and even for
           controlling loops.


           Reviewing the Command

           When you select /Data Fill, 1-2-3 asks you first to indicate a
           range to be filled, then for three values: the number with which
           to Start the sequence, the number to add to that number in each
           Step, and the value at which to Stop filling the range.

           Two facts about this command make it very useful in macros.  (1) When it
          asks for numbers, you can type formulas (including cell references),
          functions or range names.  When 1-2-3 fills the range, it evaluates the
          formulas and puts numbers, not formulas, in the cells in the range.  (2) If
          the fill range is just a single cell, it simply puts the value of the Start
          formula in that cell, and ignores the matter of Step and Stop values.

           In a macro to put the value of "FORMULA" in the cell named CELL,
           just type:

                   /dfCELL~FORMULA~~~

           Note the tildes -- one after the name of the range (CELL) to get
           the value, one after the formula, and two more for Start and Step.


           The Datestamp Macro (\D)

           The last "Tips" column suggested setting up standard worksheet
           templates for letters and memos, including a cell with @TODAY
           formatted to show the date.  If you then tell the computer the
           current date when you start it up, printed copies of these
           template-produced documents will always indicate the date of
           printing.

           But what happens if you save the newly written memo under a
           different name from that of the template?  No matter when you
           retrieve it, you see the date of the memo's creation.  However,
           the Datestamp macro offers the ability to automatically print the
           present date.  You still use a cell formatted to show Dates, only
           this time you put in the value @TODAY, not the formula.  Name the
           cell DATE.  Here's the macro:

                \D     /dfDATE~@TODAY~~~     Put the value of @TODAY in the cell
                                             named DATE.

















           Counting Loops

           In the last column, we wrote a macro that "looped" until it found
           a particular value in a cell.  Sometimes, however, you want to
           stop a macro from repeating after it has looped a particular
           number of times.  To enable 1-2-3 to count the number of loops,
           put the value for the maximum number of loops in a cell named
           LIMIT, and keep the count in a cell named COUNT.  Every time the
           macro repeats, you want 1-2-3 to add one to COUNT, i.e. increment
           the count.  Next, put an /xi (if) command in the macro to stop it
           when COUNT > LIMIT.

           Because range names can be used in formulas, /Data Fill can be
           useful in this counting macro.  To increment COUNT, just type:

                     /dfCOUNT~COUNT+1~~~       Compute the value of COUNT+1 and
                                               place it in the cell named COUNT.

           Here are the macro instructions that control the loop:

                    /dfCOUNT~1~~~                      Start the counter at one.
           LOOP     [whatever macro instructions       Cell named LOOP.
                    are to be repeated]
                    /dfCOUNT~COUNT+1~~~                Add one to the counter.
                    /xi(COUNT>LIMIT)~/xq               Stop when COUNT exceeds LIMIT.
                    /xgLOOP~                           Otherwise, repeat the LOOP.

           This is only one of many versions of a loop with a counter.  You
           could, for example, ask the user for the value to put in LIMIT
           (use the /xn command), or have loops within loops.  The /Data Fill
           command boosts the programming power of 1-2-3's macros.


           Non-Macro tips with [End]

           As you have probably learned, the [End] key is a great help in
           getting around the worksheet.  If you are at the top of a column
           of numbers, typing [End] [Down] takes you to the bottom of the
           column.  If you are in an empty cell, [End] followed by a
           cursor-motion key takes you to the first non-empty cell in that
           direction.  Here are two more uses of [End] that you may not have
           discovered: copying formulas in adjacent columns and finding the
           bottom of a range.

           In working with spreadsheets, it is common to have a formula that
           you want to copy adjacent to every cell in a column, or under
           every cell in a row.  To do so you put the cell pointer on the
           formula, select /Copy, and then press [Return] to indicate the
           formula to be copied.  Then you point out the range to which to
















           copy the formula.  The problem is that there's usually nothing in
           that range yet, so you can't use [End].  Or can you?

           To use [End] to copy formulas adjacent to a column, anchor the top
           of the new column of formulas with [.].  Then, using the
           cursor-motion keys, put the free end of the range in the column
           that already has values.  Press [End] [Down] to tack down the free
           end of that column, and use a cursor-motion key, usually [Right]),
           to bring the free end into the column you want to copy to.  Since
           that's the right range, press [Return] and you're done.

           Sounds complicated?  Actually it's harder to read about than to
           do.  Here's an example.  You have numbers in columns A and B, for
           a large number of rows.  You want to put a formula in every cell
           of column C, next to those numbers.  Give it a try.

           1) Go to the first row in column C and type the formula, say
           +A1+B1.  2) Select /Copy.  3) Press [Return] [.].  4) Press [Left]
           [End] [Down] [Right] [Return].

           You can also use [End] to find the bottom of a range.  Because the
           presence of blank cells can slow the process if you start from the
           top of the block of data, it's best to approach using [End] from
           the bottom up.  Like the above tip, this is easier to do than to
           read about.  Just try it.  To find the bottom of a column with
           blank cells in it simply

           Page down past the end and press [End] [Up].

           Or, if a nearby column is empty,

           1) Go to the end of the empty column.  2) Press [End] [Down] to
           get to the bottom of the worksheet.  3) Move back to the column
           whose end you want to locate.  4) Press [End] [Up].

           In Summary

           The [End] key techniques are very handy in day-to-day work with
           1-2-3.  What's more, you can use the very same tricks with
           Symphony.  The /Data Fill command is a great tool for macro
           writers.  By the way, when the 1-2-3 Manual was written, we hadn't
           discovered all the ways /Data Fill could be used.  It all points
           out one of the things I like about 1-2-3 -- it is always new ...
           there are still discoveries to be made.